
package org.madason.humanresources.datalayer;

import java.util.*;
import java.sql.*;
import java.util.Date;

/**
 * 
 */
public class EmployeeAccessOracle extends EmployeeAccess
{
	protected java.sql.Timestamp convertDate(java.util.Date	date)
	{
		if (date == null) return null;
		else return new java.sql.Timestamp(date.getTime());
	}

	protected java.util.Date convertDate(java.sql.Timestamp	date)
	{
		if (date == null) return null;
		else return new java.util.Date(date.getTime());
	}
		
	
    /**
     * insert new Employee
     */    
	public void insert(Connection conn, Employee bean) throws Exception
	{
		// check whether bean is an subclass class
		
		insertIntern(conn, bean);
	}


    /**
     * internal implementation of insert
     */
	public void insertIntern(Connection conn, Employee bean) throws Exception
	{

		String sqlCmd =
			"INSERT INTO \"EMPLOYEES\" (\"EMPLOYEE_ID\", \"FIRST_NAME\", \"LAST_NAME\", \"EMAIL\", \"PHONE_NUMBER\", \"HIRE_DATE\", \"SALARY\", \"MANAGER_ID\", \"JOB_ID\") VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

		PreparedStatement stm = null;

		try
		{
			stm = conn.prepareStatement("SELECT \"EMPLOYEES_SEQ\".nextval FROM dual");
			ResultSet rs = stm.executeQuery();
			rs.next();
			bean.setId(rs.getInt(1));
			rs.close();
			stm.close();			
			
			stm = conn.prepareStatement(sqlCmd);
        
        

			stm.setInt(1, bean.getId());
			if (bean.firstName == null)
				stm.setNull(2, Types.VARCHAR);
			else
				stm.setString(2, bean.getFirstName());
			if (bean.lastName == null)
				stm.setNull(3, Types.VARCHAR);
			else
				stm.setString(3, bean.getLastName());
			if (bean.email == null)
				stm.setNull(4, Types.VARCHAR);
			else
				stm.setString(4, bean.getEmail());
			if (bean.phoneNumber == null)
				stm.setNull(5, Types.VARCHAR);
			else
				stm.setString(5, bean.getPhoneNumber());
			if (bean.hireDate == null)
				stm.setNull(6, Types.DATE);
			else
				stm.setTimestamp(6, convertDate(bean.getHireDate()));
			stm.setDouble(7, bean.getSalary());
			if (bean.getManager() == 0)
				stm.setNull(8, Types.INTEGER);
			else
				stm.setInt(8, bean.getManager());
			if (bean.getJob() == null)
				stm.setNull(9, Types.VARCHAR);
			else
				stm.setString(9, bean.getJob());				
			
			stm.executeUpdate();
		}
		finally
		{
			if (stm != null) stm.close();
		}
	}
	

	public void delete(Connection conn, int primaryKey) throws Exception
	{			
		PreparedStatement stm = null;

		try
		{
			String sqlCmd =
				"DELETE FROM \"EMPLOYEES\" WHERE \"EMPLOYEE_ID\" = ?";

            stm = conn.prepareStatement(sqlCmd);

			stm.setInt(1, primaryKey);
			stm.executeUpdate();
		}
		finally
		{
			if (stm != null) stm.close();
		}
		
	}


	public void update(Connection conn, Employee bean) throws Exception
	{
		// check whether bean is an subclass class
		
		updateIntern(conn, bean);
	}


	public void updateIntern(Connection conn, Employee bean) throws Exception
	{				

		PreparedStatement stm = null;

		try
		{                    
			
			String sqlCmd =
				"UPDATE \"EMPLOYEES\" "+
				"SET \"FIRST_NAME\" = ?, \"LAST_NAME\" = ?, \"EMAIL\" = ?, \"PHONE_NUMBER\" = ?, \"HIRE_DATE\" = ?, \"SALARY\" = ?, \"MANAGER_ID\" = ?, \"JOB_ID\" = ? "+
				"WHERE \"EMPLOYEE_ID\" = ? " +                    
				"";
			stm = conn.prepareStatement(sqlCmd);

			if (bean.getFirstName() == null)				
				stm.setString(1, bean.getFirstName());			
			else
				stm.setString(1, bean.getFirstName());			
			if (bean.getLastName() == null)				
				stm.setString(2, bean.getLastName());			
			else
				stm.setString(2, bean.getLastName());			
			if (bean.getEmail() == null)				
				stm.setString(3, bean.getEmail());			
			else
				stm.setString(3, bean.getEmail());			
			if (bean.getPhoneNumber() == null)				
				stm.setString(4, bean.getPhoneNumber());			
			else
				stm.setString(4, bean.getPhoneNumber());			
			if (bean.getHireDate() == null)				
				stm.setTimestamp(5, convertDate(bean.getHireDate()));			
			else
				stm.setTimestamp(5, convertDate(bean.getHireDate()));			
			stm.setDouble(6, bean.getSalary());				
				

				if (bean.getManager() == 0)
					stm.setNull(7, Types.INTEGER);
				else
					stm.setInt(7, bean.getManager());
				stm.setString(8, bean.getJob());				

			stm.setInt(9, bean.getId());						
                    	                    
			int count = stm.executeUpdate();
			if (count == 0) throw new Exception("Der Datensatz wurde zwischzeitlich bearbeitet oder geloescht");
		}
		finally
		{
			if (stm != null) stm.close();
		}
	}
		
	

		
				
		
	
	public List<Employee> findAll(Connection conn) throws Exception
	{
		List<Employee> results = new ArrayList<Employee>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"EMPLOYEES\".\"EMPLOYEE_ID\", \"EMPLOYEES\".\"FIRST_NAME\", \"EMPLOYEES\".\"LAST_NAME\", \"EMPLOYEES\".\"EMAIL\", \"EMPLOYEES\".\"PHONE_NUMBER\", \"EMPLOYEES\".\"HIRE_DATE\", \"EMPLOYEES\".\"SALARY\", \"EMPLOYEES\".\"MANAGER_ID\", \"EMPLOYEES\".\"JOB_ID\" FROM \"EMPLOYEES\"";

			stm = conn.prepareStatement(sqlCmd);

			rs = stm.executeQuery();
											
			int idx_Employee_id = rs.findColumn("EMPLOYEE_ID");
			int idx_Employee_firstName = rs.findColumn("FIRST_NAME");
			int idx_Employee_lastName = rs.findColumn("LAST_NAME");
			int idx_Employee_email = rs.findColumn("EMAIL");
			int idx_Employee_phoneNumber = rs.findColumn("PHONE_NUMBER");
			int idx_Employee_hireDate = rs.findColumn("HIRE_DATE");
			int idx_Employee_salary = rs.findColumn("SALARY");
			int idx_Employee_manager = rs.findColumn("MANAGER_ID");
			int idx_Employee_job = rs.findColumn("JOB_ID");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Employee bean = Employee.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Employee_id));
				bean.setFirstName(rs.getString(idx_Employee_firstName));
				bean.setLastName(rs.getString(idx_Employee_lastName));
				bean.setEmail(rs.getString(idx_Employee_email));
				bean.setPhoneNumber(rs.getString(idx_Employee_phoneNumber));
				bean.setHireDate(convertDate(rs.getTimestamp(idx_Employee_hireDate)));
				bean.setSalary(rs.getDouble(idx_Employee_salary));
				bean.setManager(rs.getInt(idx_Employee_manager));
				bean.setJob(rs.getString(idx_Employee_job));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public Employee findByPrimaryKey(Connection conn, int id) throws Exception
	{

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"EMPLOYEES\".\"EMPLOYEE_ID\", \"EMPLOYEES\".\"FIRST_NAME\", \"EMPLOYEES\".\"LAST_NAME\", \"EMPLOYEES\".\"EMAIL\", \"EMPLOYEES\".\"PHONE_NUMBER\", \"EMPLOYEES\".\"HIRE_DATE\", \"EMPLOYEES\".\"SALARY\", \"EMPLOYEES\".\"MANAGER_ID\", \"EMPLOYEES\".\"JOB_ID\" FROM \"EMPLOYEES\" WHERE \"EMPLOYEES\".\"EMPLOYEE_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setInt(1, id);
			rs = stm.executeQuery();
											
			int idx_Employee_id = rs.findColumn("EMPLOYEE_ID");
			int idx_Employee_firstName = rs.findColumn("FIRST_NAME");
			int idx_Employee_lastName = rs.findColumn("LAST_NAME");
			int idx_Employee_email = rs.findColumn("EMAIL");
			int idx_Employee_phoneNumber = rs.findColumn("PHONE_NUMBER");
			int idx_Employee_hireDate = rs.findColumn("HIRE_DATE");
			int idx_Employee_salary = rs.findColumn("SALARY");
			int idx_Employee_manager = rs.findColumn("MANAGER_ID");
			int idx_Employee_job = rs.findColumn("JOB_ID");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Employee bean = Employee.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Employee_id));
				bean.setFirstName(rs.getString(idx_Employee_firstName));
				bean.setLastName(rs.getString(idx_Employee_lastName));
				bean.setEmail(rs.getString(idx_Employee_email));
				bean.setPhoneNumber(rs.getString(idx_Employee_phoneNumber));
				bean.setHireDate(convertDate(rs.getTimestamp(idx_Employee_hireDate)));
				bean.setSalary(rs.getDouble(idx_Employee_salary));
				bean.setManager(rs.getInt(idx_Employee_manager));
				bean.setJob(rs.getString(idx_Employee_job));
																																		
				
				return bean;
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return null;
	}		
	
	
	public List<Employee> findByManager(Connection conn, int manager) throws Exception
	{
		List<Employee> results = new ArrayList<Employee>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"EMPLOYEES\".\"EMPLOYEE_ID\", \"EMPLOYEES\".\"FIRST_NAME\", \"EMPLOYEES\".\"LAST_NAME\", \"EMPLOYEES\".\"EMAIL\", \"EMPLOYEES\".\"PHONE_NUMBER\", \"EMPLOYEES\".\"HIRE_DATE\", \"EMPLOYEES\".\"SALARY\", \"EMPLOYEES\".\"MANAGER_ID\", \"EMPLOYEES\".\"JOB_ID\" FROM \"EMPLOYEES\" WHERE \"MANAGER_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setInt(1, manager);
			rs = stm.executeQuery();
											
			int idx_Employee_id = rs.findColumn("EMPLOYEE_ID");
			int idx_Employee_firstName = rs.findColumn("FIRST_NAME");
			int idx_Employee_lastName = rs.findColumn("LAST_NAME");
			int idx_Employee_email = rs.findColumn("EMAIL");
			int idx_Employee_phoneNumber = rs.findColumn("PHONE_NUMBER");
			int idx_Employee_hireDate = rs.findColumn("HIRE_DATE");
			int idx_Employee_salary = rs.findColumn("SALARY");
			int idx_Employee_manager = rs.findColumn("MANAGER_ID");
			int idx_Employee_job = rs.findColumn("JOB_ID");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Employee bean = Employee.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Employee_id));
				bean.setFirstName(rs.getString(idx_Employee_firstName));
				bean.setLastName(rs.getString(idx_Employee_lastName));
				bean.setEmail(rs.getString(idx_Employee_email));
				bean.setPhoneNumber(rs.getString(idx_Employee_phoneNumber));
				bean.setHireDate(convertDate(rs.getTimestamp(idx_Employee_hireDate)));
				bean.setSalary(rs.getDouble(idx_Employee_salary));
				bean.setManager(rs.getInt(idx_Employee_manager));
				bean.setJob(rs.getString(idx_Employee_job));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public List<Employee> findByManager(Connection conn, org.madason.humanresources.datalayer.Employee manager) throws Exception
	{
		List<Employee> results = new ArrayList<Employee>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"EMPLOYEES\".\"EMPLOYEE_ID\", \"EMPLOYEES\".\"FIRST_NAME\", \"EMPLOYEES\".\"LAST_NAME\", \"EMPLOYEES\".\"EMAIL\", \"EMPLOYEES\".\"PHONE_NUMBER\", \"EMPLOYEES\".\"HIRE_DATE\", \"EMPLOYEES\".\"SALARY\", \"EMPLOYEES\".\"MANAGER_ID\", \"EMPLOYEES\".\"JOB_ID\" FROM \"EMPLOYEES\" WHERE \"MANAGER_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setInt(1, manager.getId());
			rs = stm.executeQuery();
											
			int idx_Employee_id = rs.findColumn("EMPLOYEE_ID");
			int idx_Employee_firstName = rs.findColumn("FIRST_NAME");
			int idx_Employee_lastName = rs.findColumn("LAST_NAME");
			int idx_Employee_email = rs.findColumn("EMAIL");
			int idx_Employee_phoneNumber = rs.findColumn("PHONE_NUMBER");
			int idx_Employee_hireDate = rs.findColumn("HIRE_DATE");
			int idx_Employee_salary = rs.findColumn("SALARY");
			int idx_Employee_manager = rs.findColumn("MANAGER_ID");
			int idx_Employee_job = rs.findColumn("JOB_ID");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Employee bean = Employee.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Employee_id));
				bean.setFirstName(rs.getString(idx_Employee_firstName));
				bean.setLastName(rs.getString(idx_Employee_lastName));
				bean.setEmail(rs.getString(idx_Employee_email));
				bean.setPhoneNumber(rs.getString(idx_Employee_phoneNumber));
				bean.setHireDate(convertDate(rs.getTimestamp(idx_Employee_hireDate)));
				bean.setSalary(rs.getDouble(idx_Employee_salary));
				bean.setManager(rs.getInt(idx_Employee_manager));
				bean.setJob(rs.getString(idx_Employee_job));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public List<Employee> findByJob(Connection conn, String job) throws Exception
	{
		List<Employee> results = new ArrayList<Employee>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"EMPLOYEES\".\"EMPLOYEE_ID\", \"EMPLOYEES\".\"FIRST_NAME\", \"EMPLOYEES\".\"LAST_NAME\", \"EMPLOYEES\".\"EMAIL\", \"EMPLOYEES\".\"PHONE_NUMBER\", \"EMPLOYEES\".\"HIRE_DATE\", \"EMPLOYEES\".\"SALARY\", \"EMPLOYEES\".\"MANAGER_ID\", \"EMPLOYEES\".\"JOB_ID\" FROM \"EMPLOYEES\" WHERE \"JOB_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setString(1, job);
			rs = stm.executeQuery();
											
			int idx_Employee_id = rs.findColumn("EMPLOYEE_ID");
			int idx_Employee_firstName = rs.findColumn("FIRST_NAME");
			int idx_Employee_lastName = rs.findColumn("LAST_NAME");
			int idx_Employee_email = rs.findColumn("EMAIL");
			int idx_Employee_phoneNumber = rs.findColumn("PHONE_NUMBER");
			int idx_Employee_hireDate = rs.findColumn("HIRE_DATE");
			int idx_Employee_salary = rs.findColumn("SALARY");
			int idx_Employee_manager = rs.findColumn("MANAGER_ID");
			int idx_Employee_job = rs.findColumn("JOB_ID");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Employee bean = Employee.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Employee_id));
				bean.setFirstName(rs.getString(idx_Employee_firstName));
				bean.setLastName(rs.getString(idx_Employee_lastName));
				bean.setEmail(rs.getString(idx_Employee_email));
				bean.setPhoneNumber(rs.getString(idx_Employee_phoneNumber));
				bean.setHireDate(convertDate(rs.getTimestamp(idx_Employee_hireDate)));
				bean.setSalary(rs.getDouble(idx_Employee_salary));
				bean.setManager(rs.getInt(idx_Employee_manager));
				bean.setJob(rs.getString(idx_Employee_job));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
	public List<Employee> findByJob(Connection conn, org.madason.humanresources.datalayer.Job job) throws Exception
	{
		List<Employee> results = new ArrayList<Employee>();

		PreparedStatement stm = null;
		ResultSet rs = null;
		
		try
		{
			String sqlCmd = "SELECT \"EMPLOYEES\".\"EMPLOYEE_ID\", \"EMPLOYEES\".\"FIRST_NAME\", \"EMPLOYEES\".\"LAST_NAME\", \"EMPLOYEES\".\"EMAIL\", \"EMPLOYEES\".\"PHONE_NUMBER\", \"EMPLOYEES\".\"HIRE_DATE\", \"EMPLOYEES\".\"SALARY\", \"EMPLOYEES\".\"MANAGER_ID\", \"EMPLOYEES\".\"JOB_ID\" FROM \"EMPLOYEES\" WHERE \"JOB_ID\" = ?";

			stm = conn.prepareStatement(sqlCmd);

			stm.setString(1, job.getId());
			rs = stm.executeQuery();
											
			int idx_Employee_id = rs.findColumn("EMPLOYEE_ID");
			int idx_Employee_firstName = rs.findColumn("FIRST_NAME");
			int idx_Employee_lastName = rs.findColumn("LAST_NAME");
			int idx_Employee_email = rs.findColumn("EMAIL");
			int idx_Employee_phoneNumber = rs.findColumn("PHONE_NUMBER");
			int idx_Employee_hireDate = rs.findColumn("HIRE_DATE");
			int idx_Employee_salary = rs.findColumn("SALARY");
			int idx_Employee_manager = rs.findColumn("MANAGER_ID");
			int idx_Employee_job = rs.findColumn("JOB_ID");																							
						
						
			while (rs.next())
			{
				// bean anlegen							
				Employee bean = Employee.getInstance();		

																								
	
				bean.setId(rs.getInt(idx_Employee_id));
				bean.setFirstName(rs.getString(idx_Employee_firstName));
				bean.setLastName(rs.getString(idx_Employee_lastName));
				bean.setEmail(rs.getString(idx_Employee_email));
				bean.setPhoneNumber(rs.getString(idx_Employee_phoneNumber));
				bean.setHireDate(convertDate(rs.getTimestamp(idx_Employee_hireDate)));
				bean.setSalary(rs.getDouble(idx_Employee_salary));
				bean.setManager(rs.getInt(idx_Employee_manager));
				bean.setJob(rs.getString(idx_Employee_job));
																																		
				
				results.add(bean);
			}
		}
		finally
		{
			if (rs != null) rs.close();
			if (stm != null) stm.close();
		}			
		
		return results;
	}		
	
	
}
  

	





